﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace SeparateProjects
{
    public partial class Pro_Admin_Student_List : Form
    {
        SqlConnection coon = new SqlConnection(@"Data Source = DESKTOP-28VSNS5;Initial Catalog = Training_Project; 
                                                Uid = Lv; Pwd = 123456;");
        public Pro_Admin_Student_List()
        {
            InitializeComponent();
        }
        #region  预加载用户信息
        /// <summary>
        /// 预加载信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Pro_Admin_StudentList_Load(object sender, EventArgs e)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select L.UserName,S.Stu_StuInfo_Name,Se.Sex_Name,S.Stu_StuInfo_Age,
                       G.Grede_Name,S.Stu_StuInfo_Card,S.Stu_StuInfo_Birthday,
                       S.Stu_StuInfo_Address,L.Password from Stu_Login L
                       inner join Stu_StuInfo S on(S.Sid = L.Sid)
                       inner join Sex Se on(Se.Stu_StuInfo_Sex = S.Stu_StuInfo_Sex)
                       inner join Grade  G on(G.Stu_StuInfo_Grade = S.Stu_StuInfo_Grade)
                       where 1 = 1");
            SqlDataAdapter adp = new SqlDataAdapter(sb.ToString(), coon);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            this.dataGridView1.DataSource = ds.Tables[0];
            //是否允许自动创建列
            this.dataGridView1.AutoGenerateColumns = false;
            //不允许出现空行
            this.dataGridView1.AllowUserToAddRows = false;
            //整行选中
            this.dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            //是否允许多选
            this.dataGridView1.MultiSelect = false;
        }
        #endregion
        #region   根据条件搜索信息
        /// <summary>
        /// 根据条件查询信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_Search_Click(object sender, EventArgs e)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select L.UserName,S.Stu_StuInfo_Name,Se.Sex_Name,S.Stu_StuInfo_Age,
                       G.Grede_Name,S.Stu_StuInfo_Card,S.Stu_StuInfo_Birthday,
                       S.Stu_StuInfo_Address,L.Password from Stu_Login L
                       inner join Stu_StuInfo S on(S.Sid = L.Sid)
                       inner join Sex Se on(Se.Stu_StuInfo_Sex = S.Stu_StuInfo_Sex)
                       inner join Grade  G on(G.Stu_StuInfo_Grade = S.Stu_StuInfo_Grade)
                       where 1 = 1");
            if (!string.IsNullOrWhiteSpace(this.txt_Sid.Text))
            {
                sb.AppendFormat("and L.UserName like '%{0}%'", txt_Sid.Text.Trim());
            }
            if (!string.IsNullOrWhiteSpace(this.txt_Name.Text))
            {
                sb.AppendFormat(" and S.Stu_StuInfo_Name like '%{0}%'", txt_Name.Text.Trim());
            }
            SqlDataAdapter adp = new SqlDataAdapter(sb.ToString(), coon);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            this.dataGridView1.DataSource = ds.Tables[0];
        }
        #endregion
    }
}
